--
-- PostgreSQL PSA initial database
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;


--CREATE PROCEDURAL LANGUAGE plpgsql;
--ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;


SET search_path = public, pg_catalog;


CREATE FUNCTION unix_timestamp(timestamp without time zone) RETURNS integer
    AS $_$SELECT date_part('epoch', $1)::integer;$_$
    LANGUAGE sql IMMUTABLE;


SET default_tablespace = '';
SET default_with_oids = false;



CREATE TABLE psa_group (
    psa_id integer NOT NULL,
    psa_name character varying(50) NOT NULL,
    psa_serialized_data text
);
COMMENT ON COLUMN psa_group.psa_id IS 'Group ID';
COMMENT ON COLUMN psa_group.psa_name IS 'Group name';
COMMENT ON COLUMN psa_group.psa_serialized_data IS 'Serialized group object';



CREATE SEQUENCE psa_group_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER SEQUENCE psa_group_id_seq OWNED BY psa_group.psa_id;
SELECT pg_catalog.setval('psa_group_id_seq', 1, true);



CREATE TABLE psa_log (
    id integer NOT NULL,
    log_time timestamp without time zone,
    message text,
    client_ip character varying(40),
    user_id integer,
    username character varying(50),
    group_id integer,
    groupname character varying(50),
    function text,
    type character varying(20) DEFAULT 'general'::character varying NOT NULL,
    request_uri character varying(250),
    user_agent character varying(150),
    referer character varying(250)
);
COMMENT ON COLUMN psa_log.log_time IS 'Log creation time';
COMMENT ON COLUMN psa_log.message IS 'Log message';
COMMENT ON COLUMN psa_log.client_ip IS 'Client ip address';
COMMENT ON COLUMN psa_log.user_id IS 'User ID';
COMMENT ON COLUMN psa_log.username IS 'Username';
COMMENT ON COLUMN psa_log.group_id IS 'Group ID';
COMMENT ON COLUMN psa_log.groupname IS 'Group name';
COMMENT ON COLUMN psa_log.function IS 'Function, method or debug backtrace that created log';
COMMENT ON COLUMN psa_log.type IS 'Type of log message. Some types used by psa are: ''general'', ''error'', ''warning''';



CREATE SEQUENCE psa_log_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER SEQUENCE psa_log_id_seq OWNED BY psa_log.id;
SELECT pg_catalog.setval('psa_log_id_seq', 1, true);



CREATE TABLE psa_plugin (
    id integer NOT NULL,
    plugins_data text
);
COMMENT ON COLUMN psa_plugin.plugins_data IS 'Serialized data about all plugins';



CREATE TABLE psa_profile_log (
    id integer NOT NULL,
    method character varying(100),
    method_arguments text,
    total_time real,
    client_ip character varying(40),
    log_time timestamp without time zone,
    request_id character varying(50)
);
COMMENT ON COLUMN psa_profile_log.method IS 'Plugin method';
COMMENT ON COLUMN psa_profile_log.method_arguments IS 'Arguments passed to the plugin method. Output of print_r() function.';
COMMENT ON COLUMN psa_profile_log.total_time IS 'Total execution time of the method in seconds';
COMMENT ON COLUMN psa_profile_log.client_ip IS 'Client ip address';
COMMENT ON COLUMN psa_profile_log.log_time IS 'Log creation time';



CREATE SEQUENCE psa_profile_log_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER SEQUENCE psa_profile_log_id_seq OWNED BY psa_profile_log.id;
SELECT pg_catalog.setval('psa_profile_log_id_seq', 1, true);



CREATE TABLE psa_user (
    psa_id integer NOT NULL,
    psa_username character varying(50) NOT NULL,
    psa_password character varying(250) NOT NULL,
    psa_last_login timestamp without time zone,
    psa_serialized_data text
);
COMMENT ON COLUMN psa_user.psa_id IS 'User ID';
COMMENT ON COLUMN psa_user.psa_username IS 'Username';
COMMENT ON COLUMN psa_user.psa_password IS 'Password hash';
COMMENT ON COLUMN psa_user.psa_last_login IS 'Date and time when user has last logged in.';
COMMENT ON COLUMN psa_user.psa_serialized_data IS 'Serialized user object';



CREATE SEQUENCE psa_user_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER SEQUENCE psa_user_id_seq OWNED BY psa_user.psa_id;
SELECT pg_catalog.setval('psa_user_id_seq', 1, true);



CREATE TABLE psa_user_in_group (
    user_id integer NOT NULL,
    group_id integer DEFAULT 1 NOT NULL
);


ALTER TABLE psa_group ALTER COLUMN psa_id SET DEFAULT nextval('psa_group_id_seq'::regclass);

ALTER TABLE psa_log ALTER COLUMN id SET DEFAULT nextval('psa_log_id_seq'::regclass);

ALTER TABLE psa_profile_log ALTER COLUMN id SET DEFAULT nextval('psa_profile_log_id_seq'::regclass);

ALTER TABLE psa_user ALTER COLUMN psa_id SET DEFAULT nextval('psa_user_id_seq'::regclass);

INSERT INTO psa_group (psa_id, psa_name, psa_serialized_data) VALUES (1, 'psa', NULL);

INSERT INTO psa_plugin (id, plugins_data) VALUES (1, NULL);

INSERT INTO psa_user (psa_id, psa_username, psa_password, psa_last_login, psa_serialized_data) VALUES (1, 'psa', 'a54ed3383c2aa9fc7aa86257ffae23f08b833255d8b20729cf7128c908123749', NULL, NULL);

INSERT INTO psa_user_in_group (user_id, group_id) VALUES (1, 1);

ALTER TABLE ONLY psa_group
    ADD CONSTRAINT psa_group_pkey PRIMARY KEY (psa_id);

ALTER TABLE ONLY psa_log
    ADD CONSTRAINT psa_log_pkey PRIMARY KEY (id);

ALTER TABLE ONLY psa_plugin
    ADD CONSTRAINT psa_plugin_pkey PRIMARY KEY (id);

ALTER TABLE ONLY psa_profile_log
    ADD CONSTRAINT psa_profile_log_pkey PRIMARY KEY (id);

ALTER TABLE ONLY psa_user_in_group
    ADD CONSTRAINT psa_user_in_group_pkey PRIMARY KEY (group_id, user_id);

ALTER TABLE ONLY psa_user
    ADD CONSTRAINT psa_user_pkey PRIMARY KEY (psa_id);

CREATE INDEX group_id ON psa_user_in_group USING btree (group_id);

CREATE UNIQUE INDEX groupname ON psa_group USING btree (psa_name);

CREATE INDEX user_id ON psa_user_in_group USING btree (user_id);

CREATE UNIQUE INDEX username ON psa_user USING btree (psa_username);

ALTER TABLE ONLY psa_user_in_group
    ADD CONSTRAINT "FK_psa_user_in_group_gid" FOREIGN KEY (group_id) REFERENCES psa_group(psa_id) ON DELETE CASCADE;

ALTER TABLE ONLY psa_user_in_group
    ADD CONSTRAINT "FK_psa_user_in_group_uid" FOREIGN KEY (user_id) REFERENCES psa_user(psa_id) ON DELETE CASCADE;
